﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 01.04.2011
-- Description:	Отчет Счета-фактуры
-- =============================================
CREATE PROCEDURE [dbo].[report_vat_invoices] 
	@p_date_from date,
	@p_date_to date,
	@p_nsi_organization_id int,
	@p_contract_id int,
	@p_type_of_activity int,
	@p_vat_inv_type int
AS
BEGIN
	SET NOCOUNT ON;

    SELECT o.SHORT_NAME as ORG_NAME, c.NUMBER as CONTRACT_NUM, vi.NUMBER AS VI_NUMBER, vi.[DATE] as VI_DATE, vi.EXEC_NUMBER, null as EXEC_DATE,
			pu.NAME AS PRODUCT_NAME, ser.SHORT_NAME as SERV_NAME, vis.QUANTITY as SERV_COUNT, vi.TYPE,
			COUNT(pu.NAME) as PRODUCT_COUNT,
			vi.SUM_RUB as VAT_SUM_RUB,
			ta.NAME as NSI_TYPE_OF_ACTIVITY_NAME,
			vi.SUM_RUB/[dbo].[CountVIPepeatsInRepVatInv](vi.ID) as PROPORTIONAL_SUM,
			SUM(pu.PAYMENT_IN_SUM_RUB)as PAY_SUM_RUB
    FROM VAT_INVOICE as vi
    LEFT JOIN PID ON vi.PID = PID.ID 
    LEFT JOIN [CONTRACT] AS c ON PID.ID = c.PID 
	LEFT JOIN NSI_ORGANIZATION AS o ON vi.NSI_ORGANIZATION_ID = o.ID
	LEFT JOIN VIEW_SEARCH_P_U_FOR_FORMS as pu ON vi.ID = pu.VAT_INVOICE_ID
	LEFT JOIN dbo.NSI_TYPE_OF_ACTIVITY as ta ON ta.ID = c.NSI_TYPE_OF_ACTIVITY_ID
	LEFT JOIN VAT_INV_ON_SERVICE as vis ON vis.VAT_INVOICE_ID = vi.ID
	LEFT JOIN NSI_SERVICE as ser ON vis.NSI_SERVICE_ID = ser.ID
	WHERE vi.[DATE] BETWEEN @p_date_from AND @p_date_to
	AND 
	(
		(@p_nsi_organization_id is null)
		OR
		(vi.NSI_ORGANIZATION_ID = @p_nsi_organization_id)
	)
	AND
	(
		(@p_contract_id is null)
		OR
		(c.ID = @p_contract_id)
	)
	AND
	(
		(@p_type_of_activity is null)
		OR
		(ta.ID = @p_type_of_activity)
	)
	AND
	(
		(@p_vat_inv_type is null)
		OR -- Товары
		(@p_vat_inv_type = 1 AND vi.[TYPE] = 'Товары/Работы' AND pu.TYPE = 1)
		OR -- Работы
		(@p_vat_inv_type = 2 AND vi.[TYPE] = 'Товары/Работы' AND pu.TYPE = 2)
		OR -- Услуги
		(@p_vat_inv_type = 3 AND vi.[TYPE] = 'Услуги')
	)
	
	GROUP BY o.SHORT_NAME, c.NUMBER, vi.NUMBER, vi.[DATE], vi.EXEC_NUMBER, pu.NAME, ta.NAME, vi.SUM_RUB, ser.SHORT_NAME, vis.QUANTITY, vi.TYPE, vi.ID
END